﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Nova_DAL
{
    public class User
    {
        int i = 0;
        string strSQL = "";
        string vUserinfo = "";        //用户信息


        DbHelper dbHelper = new DbHelper();
        DataSet ds = new DataSet();

        ///<summary>
        ///判断用户名是否存在
        ///</summary>
        ///<param name="sQL">vUserNo ,vPassWord</param>
        ///<returns>返回值</returns>
        ///Writer：[王成友]
        ///Create Date： [2010-5-19]
        ///</summary>
        public int Exist(Nova_MOD.Users Users)
        {
            try
            {
                strSQL = "select * from tbUser where vUserNo =@vUserNo and vPassWord =@vPassWord";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, Users.vUserNo);
                dbHelper.AddInParameter(cmd, "@vPassWord", DbType.String, Users.vPassWord);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    i = 1;
                }
                else
                {
                    i = 0;
                }

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;

        }

        ///<summary>
        ///查找用户名称
        ///</summary>
        ///<param name="sQL">vUserNo </param>
        ///<returns>返回用户名称: 如果返回0失败</returns>
        ///Writer：[王成友]
        ///Create Date： [2010-5-19]
        ///</summary>
        public string CallBackUserName(string vUserNo)
        {
            try
            {
                strSQL = "select vUserName from tbUser where vUserNo = @vUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, vUserNo);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    vUserinfo = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    vUserinfo = "0";
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return vUserinfo;

        }

        ///<summary>
        ///查找用户部门
        ///</summary>
        ///<param name="sQL">vUserNo </param>
        ///<returns>返回部门名称: 如果返回0失败</returns>
        ///Writer：[王成友]
        ///Create Date： [2010-5-19]
        ///</summary>
        public string CallBackDepartment(string vUserNo)
        {
            try
            {
                strSQL = "select d.vDepartment from tbUser u inner join tbDepart d on u.vDepartmentNo = d.vDepartmentNo and u.vUserNo = @vUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, vUserNo);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    vUserinfo = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    vUserinfo = "0";
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return vUserinfo;

        }

        ///<summary>
        ///查找用户工作组编号
        ///</summary>
        ///<param name="sQL">vGroupNo </param>
        ///<returns>返回工作组编号: 如果返回0失败</returns>
        ///Writer：[王成友]
        ///Create Date： [2010-5-21]
        ///</summary>
        public string CallBackGroupNo(string vUserNo)
        {
            try
            {
                strSQL = "select vGroupNo from tbUser where vUserNo = @vUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, vUserNo);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    vUserinfo = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    vUserinfo = "0";
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return vUserinfo;

        }

        /// <summary>
        /// 根据id查询用户表
        /// </summary>
        /// <param name="id"></param>
        /// <returns>返回DataSet</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        public DataSet QueryUserDataSet(string pid)
        {
            try
            {
                strSQL = @"SELECT [PID]
                                  ,[vUserNo]
                                  ,[vUserName]
                                  ,[vPassWord]
                                  ,[vDepartmentNo]
                                  ,[vGroupNo]
                                  ,[vSex]
                                  ,[vIpAddress]
                                  ,[iUserStatus]
                                  ,[DDate]
                                  ,[WID]
                              FROM [dbo].[tbUser]
                                where PID = @pid";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@pid", DbType.String, pid);
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }

        /// <summary>
        /// 根据id查询用户表
        /// </summary>
        /// <param name="id"></param>
        /// <returns>返回DataSet</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        public DataSet QueryUserDataSetByNo(string UserNo)
        {
            try
            {
                strSQL = @"SELECT [PID]
                                  ,[vUserNo]
                                  ,[vUserName]
                                  ,[vPassWord]
                                  ,[vDepartmentNo]
                                  ,[vGroupNo]
                                  ,[vSex]
                                  ,[vIpAddress]
                                  ,[iUserStatus]
                                  ,[DDate]
                                  ,[WID]
                              FROM [dbo].[tbUser]
                                where [vUserNo] = @UserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@UserNo", DbType.String, UserNo);
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }

        /// <summary>
        /// 查询所有的用户资料
        /// </summary>
        /// <returns>返回DataSet</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        public DataSet QueryAllUserDataSet()
        {
            try
            {
                strSQL = @"SELECT U.[PID]
                                  ,U.[vUserNo]
                                  ,U.[vUserName]
                                  ,U.[vPassWord]
                                  ,U.[vDepartmentNo]
                                  ,U.[vGroupNo]
                                  ,U.[vSex]
                                  ,U.[vIpAddress]
                                  ,U.[iUserStatus]
                                  ,U.[DDate],U.[WID]
                                  ,ISNULL(D.vDepartment,'') AS vDepartment
                              FROM 
                                    [dbo].[tbUser] AS U
                        LEFT JOIN 
                                    tbDepart AS D on U.[vDepartmentNo]= D.[vDepartmentNo]
                       WHERE U.vUserNo <>'admin'
                       ORDER BY U.vUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }

        /// <summary>
        /// 根据条件模糊查询用户资料
        /// </summary>
        /// <param name="vUserNo"></param>
        /// <param name="vUserName"></param>
        /// <param name="strDept"></param>
        /// <returns>DataSet</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]

        public DataSet QueryUserDataSet(string vUserNo, string vUserName, string strDept)
        {
            try
            {
                strSQL = @"SELECT U.[PID]
                                  ,U.[vUserNo]
                                  ,U.[vUserName]
                                  ,U.[vPassWord]
                                  ,U.[vDepartmentNo]
                                  ,U.[vGroupNo]
                                  ,U.[vSex]
                                  ,U.[vIpAddress]
                                  ,U.[iUserStatus]
                                  ,U.[DDate]
                                  ,U.[WID]
                                  ,ISNULL(D.vDepartment,'') AS vDepartment
                              FROM 
                                    [dbo].[tbUser] AS U
                        LEFT JOIN 
                                    tbDepart AS D on U.[vDepartmentNo]= D.[vDepartmentNo]
                            WHERE 
                                    (U.vUserNo like @vUserNo) 
                                and  (U.vUserName like @vUserName)
                                and (U.vDepartmentNo like @strDept
                                OR  D.vDepartment like @strDept)";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, "%" + vUserNo + "%");
                dbHelper.AddInParameter(cmd, "@vUserName", DbType.String, "%" + vUserName + "%");
                dbHelper.AddInParameter(cmd, "@strDept", DbType.String, "%" + strDept + "%");
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }


        ///<summary>
        ///增加用户表
        ///</summary>
        ///<param name="sQL"></param>
        ///<returns>返回int,0失败,1成功</returnss>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        ///</summary>
        public int AddUser(Nova_MOD.Users Users)
        {
            try
            {
                strSQL = @"INSERT INTO [dbo].[tbUser]
                                       ([vUserNo]
                                       ,[vUserName]
                                       ,[vPassWord]
                                       ,[vDepartmentNo]
                                       ,[vGroupNo]
                                       ,[vSex]
                                       ,[vIpAddress]
                                       ,[iUserStatus]
                                       ,[DDate]
                                       ,[WID])
                                 VALUES
                                       (@vUserNo
                                       ,@vUserName
                                       ,@vPassWord
                                       ,@vDepartmentNo
                                       ,@vGroupNo
                                       ,@vSex
                                       ,@vIpAddress
                                       ,@iUserStatus
                                       ,@DDate
                                       ,@WID)";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, Users.vUserNo);
                dbHelper.AddInParameter(cmd, "@vUserName", DbType.String, Users.vUserName);
                dbHelper.AddInParameter(cmd, "@vPassWord", DbType.String, Users.vPassWord);
                dbHelper.AddInParameter(cmd, "@vDepartmentNo", DbType.String, Users.vDepartmentNo);
                dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, Users.vGroupNo);
                dbHelper.AddInParameter(cmd, "@vSex", DbType.String, Users.vSex);
                dbHelper.AddInParameter(cmd, "@vIpAddress", DbType.String, Users.vIpAddress);
                dbHelper.AddInParameter(cmd, "@iUserStatus", DbType.Int32, Convert.ToInt32(Users.iUserStatus));
                dbHelper.AddInParameter(cmd, "@DDate", DbType.DateTime, Users.dDate);
                dbHelper.AddInParameter(cmd, "@WID", DbType.String, Users.WID);

                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///修改用户表
        ///</summary>
        ///<param name="sQL"></param>
        ///<returns>返回int,0失败,1成功</returnss>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        ///</summary>
        public int UpdateUser(Nova_MOD.Users Users, string strUserNo)
        {
            try
            {
                strSQL = @"UPDATE  [dbo].[tbUser]
                               SET [vUserNo] = @vUserNo
                                  ,[vUserName] = @vUserName
                                  ,[vPassWord] = @vPassWord
                                  ,[vDepartmentNo] = @vDepartmentNo
                                  ,[vGroupNo] = @vGroupNo
                                  ,[vSex] = @vSex
                                  ,[vIpAddress] = @vIpAddress
                                  ,[iUserStatus] = @iUserStatus
                                  ,[WID] = @WID
                             WHERE vUserNo=@strUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, Users.vUserNo);
                dbHelper.AddInParameter(cmd, "@vUserName", DbType.String, Users.vUserName);
                dbHelper.AddInParameter(cmd, "@vPassWord", DbType.String, Users.vPassWord);
                dbHelper.AddInParameter(cmd, "@vDepartmentNo", DbType.String, Users.vDepartmentNo);
                dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, Users.vGroupNo);
                dbHelper.AddInParameter(cmd, "@vSex", DbType.String, Users.vSex);
                dbHelper.AddInParameter(cmd, "@vIpAddress", DbType.String, Users.vIpAddress);
                dbHelper.AddInParameter(cmd, "@iUserStatus", DbType.Int32, Convert.ToInt32(Users.iUserStatus));
                dbHelper.AddInParameter(cmd, "@strUserNo", DbType.String, strUserNo);
                dbHelper.AddInParameter(cmd, "@WID", DbType.String, Users.WID);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }


        ///<summary>
        ///删除多个用户
        ///</summary>
        ///<param name="listGroupNo">id</param>
        ///<returns>返回int,0失败,大于等于1成功</returnss>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        ///</summary>
        public int DeleteUsers(string listId)
        {
            try
            {
                strSQL = "delete from tbUser where PID in  (" + listId + ") ";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///注销多个用户
        ///</summary>
        ///<param name="listGroupNo">id</param>
        ///<returns>返回int,0失败,大于等于1成功</returnss>
        /// writer:[Kitty]
        /// Create Date:[2010-5-29]
        ///</summary>
        public int LogoutUsers(string listId)
        {
            try
            {
                strSQL = "update tbUser set iUserStatus=0 where PID in  (" + listId + ") ";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }


        /// <summary>
        /// 检查用户编号是否已经存在
        /// </summary>
        /// <param name="strGroupNo"></param>
        /// <returns>返回int,0不存在;1存在</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        public int CheckUserNoExists(string vUserNo)
        {
            strSQL = "select isnull(count(1),0) from tbUser where vUserNo = @vUserNo";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, vUserNo);

            return Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
        }

        /// <summary>
        /// 检查用户编号是否已经存在,用于修改资料时判断
        /// </summary>
        /// <param name="strGroupNo"></param>
        /// <param name="oldGroupNo"></param>
        /// <returns>返回int,0不存在;1存在</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-28]
        public int CheckUserNoExists(string vUserNo, string oldUserNo)
        {
            strSQL = "select isnull(count(1),0) from tbUser where vUserNo = @vUserNo and vUserNo <> @oldUserNo";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, vUserNo);
            dbHelper.AddInParameter(cmd, "@oldUserNo", DbType.String, oldUserNo);

            return Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
        }

        ///<summary>
        ///修改密码
        ///</summary>
        ///<param name="sQL"></param>
        ///<returns>返回int,0失败,1成功</returnss>
        /// writer:[夏鑫]
        /// Create Date:[2010-5-28]
        ///</summary>
        public int UpdateUserPwd(Nova_MOD.Users Users)
        {
            try
            {
                strSQL = @"UPDATE  [dbo].[tbUser]
                               SET [vPassWord] = @vPassWord
                             WHERE vUserNo=@vUserNo";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vPassWord", DbType.String, Users.vPassWord);
                dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, Users.vUserNo);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        public DataTable GetByUserNoPassword(string userNo, string password)
        {
            strSQL = "select vUserNo,VGroupNo from tbUser where vUserNo =@vUserNo and vPassWord =@vPassWord";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@vUserNo", DbType.String, userNo);
            dbHelper.AddInParameter(cmd, "@vPassWord", DbType.String, password);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }
    }
}
